﻿using System;
using System.Data.Entity;
using System.Linq;
using System.Web.Mvc;
using KPIS.GERP.Models;
using KPIS.GERP.Models.BGM;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.HRM;
using System.Collections.Generic;
using KPIS.GERP.Models.ICM.ViewModel;
using Resources;
using KPIS.GERP.Models.Class;
//Adding
using Novacode;
using System.Drawing;

namespace KPIS.GERP.WEB.GIMS.Controllers
{
    // [AccessDeniedAuthorize]
    public class DeliveryNoteController : Controller
    {
        //
        // GET: /DeliveryNote/
        private string[] _basicIncludeSubclass = new[]
		    {

				"Bidder", "SysPoType"
				//"Committees", "Files", "PrePurchaseRequestItems"
				//, "RequestEmployee", "Progress"
		    };

        public class  Member
        {
            public string POName {get;set;}
           
            
        }
        public ActionResult indexNotDelivery()
        {
            try
            {
                var service = new DeliveryNoteService.DeliveryNoteServiceClient();
                var installs = service.SelectPurchaseOrderInsatall(new Models.ICM.PurchaseOrderInstallment {},
                    new[]
                    {
                        "PurchaseOrder",
                        "PurchaseOrder.MasPurchaseType",
                        "PurchaseOrder.Bidder.Bid",
                        "PurchaseOrder.Bidder.MasContact",
                        "PurchaseOrder.Bidder.PurchaseRequest"
                    }).ToList()
                    // .Where(o =>new[] {6792, 6793, 6794, 6795, 6796}.Contains(o.PoInstallId))
                    .ToList();

                var allDeliveryNotes = service.SelectDeliveryNote(new Models.ICM.DeliveryNote { RecordStatus = "!D" }, null); 
                 
                installs = installs.Where(x => x.PurchaseOrder.RecordStatus == "A").ToList(); 
                if (installs.Any())
                {
                    foreach (DeliveryNote item in allDeliveryNotes)
                    {
                        var a = installs.FirstOrDefault(x => x.PoInstallId == item.PoInstallId);
                        if (a != null)
                        { 
                            installs.Remove(a);
                        }
                    }
                } 
                return View(installs);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public ActionResult Index()
        {
            try
            {
                var service = new DeliveryNoteService.DeliveryNoteServiceClient();
                var model = service.SelectDeliveryNote(new Models.ICM.DeliveryNote { RecordStatus = "!D" }, new[] { "PurchaseOrder.Bidder.PurchaseRequest", "PurchaseOrderInstallment", "PurchaseOrder.MasPurchaseType", "PurchaseOrder.Bidder.MasContact" });
                var poInstallmentsOfPo = new PurchaseOrderService.PurchaseOrderServiceClient().SelectPoInstallment(null, null);

                ViewBag.PoInstallmentsOfPo = poInstallmentsOfPo;
                return View(model);

            }
            catch (Exception ex)
            {
                throw ex;
            }
           

           // return View(service.SelectDeliveryNote(new Models.ICM.DeliveryNote { RecordStatus = "!D" }, new[] { " PurchaseOrder", "PurchaseOrderInstallment" }));

        }


        public ActionResult Details(int? PoId, int? PoInstallId,int DnId, bool nolayout = false)
        {
            //var service = new DeliveryNoteService.DeliveryNoteServiceClient();
            //var model=service.SelectDeliveryNote(new Models.ICM.DeliveryNote { DnId =id},new [] {" PurchaseOrder", "PurchaseOrderInstallment"}).FirstOrDefault();
            //DeliveryNote oModel = new DeliveryNote();

            //oModel.PoId = PoId;
            //oModel.PoInstallId = PoInstallId.Value;
            //oModel.DnId = DnId.Value;

           var service = new DeliveryNoteService.DeliveryNoteServiceClient();
           var model = service.SelectDeliveryNote(new Models.ICM.DeliveryNote { DnId = DnId }
               , new[] { "PurchaseOrder", "PurchaseOrderInstallment", "PurchaseOrder.MasPurchaseType", "PurchaseOrder.Bidder.Bid", "PurchaseOrder.Bidder.MasContact", "PurchaseOrder.Bidder.PurchaseRequest", "Warehouse" }).FirstOrDefault();

           ViewBag.NoLayout = nolayout;

            return View(model);

        }

        //public ActionResult _EditorForm(int id)
        //{
        //    return Details(id, true);
        //}

        //public ActionResult Details(int id, bool isPartial = false)
        //{
        //    var service = new PrePurchaseRequestService.PrePurchaseRequestServiceClient();
        //    var model = service.Select(new Models.ICM.PrePurchaseRequest { PprId = id }, _basicIncludeSubclass).FirstOrDefault();

        //    if (isPartial)
        //    {
        //        return PartialView("_DetailsForm", model);
        //    }
        //    return View(model);
        //}
        public ActionResult _CreateDetail( int? DnId)
        {
            var service = new DeliveryNoteService.DeliveryNoteServiceClient();
            var item = service.SelectDeliveryNote(new Models.ICM.DeliveryNote { DnId = DnId.Value, RecordStatus = "!D" }, new[] { "PurchaseOrder.Bidder.MasContact" }).FirstOrDefault();
            if (item == null)
            {
                return HttpNotFound();

            }
            return View(item);
        }

        public ActionResult Edit(int DnId)
        {
            var service = new DeliveryNoteService.DeliveryNoteServiceClient();
            var item = service.SelectDeliveryNote(new Models.ICM.DeliveryNote { DnId = DnId, RecordStatus = "!D" }, new[] { "PurchaseOrder" }).FirstOrDefault();

            if (item == null)
            {
                return HttpNotFound();
            }
            return View(item);
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit(int DnId,DeliveryNote model)
        {

            try
            {
                if (ModelState.IsValid)
                {
                    if (model.WhId == null)
                    {
                        return Json(new { result = false, message = "กรุณากรอก [คลังพัสดุที่จะรับเข้าคลัง]", alertClass = "" });
                    }

                    var service = new DeliveryNoteService.DeliveryNoteServiceClient();
                    var dnCode = service.SelectDeliveryNote(new DeliveryNote {DnId = model.DnId}, null).First();
                    if (model.Code != dnCode.Code)
                    {
                        model.Code = dnCode.Code;
                    }
                    service.Update(model, Class.GimsMembership.CurrentEmpId);

                    return RedirectToAction("Index");
                }
                return Edit(model.DnId);
            }
            catch
            {
                return Edit(model.DnId);
            }
        }

        public ActionResult About()
        {
            return View();
        }

        public ActionResult Create(int? PoId, int? PoInstallId)
        {
            DeliveryNote oModel = new DeliveryNote();
            using (var service = new DeliveryNoteService.DeliveryNoteServiceClient())
            {
                var PIOrder = service.SelectPurchaseOrderInsatall(new Models.ICM.PurchaseOrderInstallment(), new[] { "PurchaseOrder" }).ToList();
                var OrderI = PIOrder.Where(o => o.PurchaseOrder.PoId == PoId).FirstOrDefault();
                oModel.DocumentNumber = OrderI.PurchaseOrder.Code;
                oModel.PurchaseOrder = OrderI.PurchaseOrder;
                oModel.DeliveryDate = OrderI.DeliveryDate;
            }
            if(String.IsNullOrWhiteSpace(oModel.DeliveryPlace))
                oModel.DeliveryPlace = "สถาบันรับรองคุณภาพสถานพยาบาล (องค์การมหาชน)";

            oModel.PoId = PoId;
            oModel.PoInstallId = PoInstallId;

            return View(oModel);
        }


        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(DeliveryNote model)
        {
            if (ModelState.IsValid)
            {
                try
                {
                    var service = new DeliveryNoteService.DeliveryNoteServiceClient();

                    if (model.WhId == null)
                    {
                        return Json(new { result = false, message = "กรุณากรอก [คลังพัสดุที่จะรับเข้าคลัง]", alertClass = "" });
                    }

                    // model = 1;// new UserService.UserServiceClient().GetEmployeeByUser(Class.GimsMembership.CurrentEmpId).EmpId;
                     model.DnId = service.Insert(model, Class.GimsMembership.CurrentEmpId);
                    
                    return Json(new { result = true, message = "บันทึกเรียบร้อยแล้ว", alertClass = "" });
                    //return RedirectToAction("Index", new { id });
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return View(model);
        }

        public ActionResult Delete(int id)
        {
            try
            {
                var service = new DeliveryNoteService.DeliveryNoteServiceClient();
                service.DeleteDeliveryNote(id, Class.GimsMembership.CurrentEmpId);

                return RedirectToAction("Index");
            }
            catch
            {
                return Index();
            }
        }

        [HttpPost]
        public ActionResult DeleteAll(int[] deleteIds)
        {
            try
            {
                if (deleteIds != null)
                {
                    var service = new DeliveryNoteService.DeliveryNoteServiceClient();

                    foreach (int delete in deleteIds)
                    {
                        service.DeleteDeliveryNote(delete, Class.GimsMembership.CurrentEmpId);
                    }
                }

                ModelState.AddModelError("Error", "ลบข้อมูลไปทั้งหมด " + deleteIds.Length.ToString("#,##0") + " รายการ");

                return Json(new { success = true });
            }
            catch
            {
                ModelState.AddModelError("Error", "พบข้อผิดพลาดขณะลบข้อมูล");
                return Json(new { success = false });
            }
        }

        public ActionResult SendToMoneyRequest(int Id)
        {
            try
            {
                #region[old]
                List<DeliveryNote> dn = new List<DeliveryNote>();
                using (var service = new DeliveryNoteService.DeliveryNoteServiceClient())
                {
                    dn = service.SelectDeliveryNote(new DeliveryNote { DnId = Id }, null).ToList();
                }
                //List<PurchaseOrder> po = new List<PurchaseOrder>();
                List<PurchaseOrderInstallmentItem> poIns = new List<PurchaseOrderInstallmentItem>();
                using (var posrv = new PurchaseOrderService.PurchaseOrderServiceClient())
                {
                    //po = posrv.Select(new Models.ICM.PurchaseOrder { PoId = (int)model.PoId }, new[] { "Bidder", "MasPurchaseType", "DurationType" }).ToList();
                    poIns = posrv.SelectPoInstallmentItem(new Models.ICM.PurchaseOrderInstallmentItem { PoInstallId = (int)dn.FirstOrDefault().PoInstallId }, new[] { "BidderQuotation", "BidderQuotation.Bidder" }).ToList();
                }
                //List<PurchaseOrderItem> poItem = new List<PurchaseOrderItem>();
                //using (var posrv = new PurchaseOrderItemService.PurchaseOrderItemServiceClient())
                //{
                //    poItem = posrv.Select(new Models.ICM.PurchaseOrderItem { PoId = (int)model.PoId }, new[] { "BidderQuotation", "BidderQuotation.PurchaseRequestItem", "BidderQuotation.PurchaseRequestItem" }).ToList();
                //}

                List<PurchaseRequestItem> prItem = new List<PurchaseRequestItem>();
                using (var prsrv = new PurchaseRequestItemService.PurchaseRequestItemServiceClient())
                {
                    prItem = prsrv.Select(new Models.ICM.PurchaseRequestItem { PrItemId = (int)poIns.FirstOrDefault().BidderQuotation.PrItemId }, new[] { "PurchaseRequest", "MasBudgetExpense" }).ToList();
                }
                //get ppritem data
                List<PrePurchaseRequestItem> pprItem = new List<PrePurchaseRequestItem>();
                using (var ppritem = new PrePurchaseRequestItemService.PrePurchaseRequestItemServiceClient())
                {
                    pprItem = ppritem.Select(new PrePurchaseRequestItem { PrItemId = poIns.FirstOrDefault().BidderQuotation.PrItemId }, new[] { "ProjectOperationExpense" }).ToList();
                }
                #endregion[old]

                string RefNo = string.Empty;
                using (var sys = new SysMasterService.SysMasterServiceClient())
                {
                    RefNo = sys.GenNewRefNo2("fim_money_requests", "RQ", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                }

                #region[assign model]
                /////////model money_request
                MoneyRequest mr = new MoneyRequest();
                mr.AdvanceBgSourceId = null;
                mr.Allow = "1";
                mr.ApproveId = null;
                mr.BgExpGrpId = null; ///////bgm_mas_expense_group
                mr.BgProjOperId = pprItem.FirstOrDefault().ProjectOperationExpense.ProjOperId;
                mr.BgSourceId = pprItem.FirstOrDefault().BgSourceId;
                mr.BgYear = prItem.FirstOrDefault().PurchaseRequest.BgYear;
                mr.BorrowEndDate = null;
                mr.BorrowNo = null;
                mr.BorrowReturnDate = null;
                mr.BorrowStartDate = null;
                mr.BorrowTypeId = null;
                mr.ContactId = poIns.FirstOrDefault().BidderQuotation.Bidder.ConId;//model.PurchaseOrder.Bidder.ConId;
                mr.CreatedBy = Class.GimsMembership.CurrentEmpId;
                mr.CreatedWhen = DateTime.Now;
                mr.CreditCardNo = null;
                mr.DebtorContactId = null;
                mr.DebtorEmpId = null;
                mr.DnId = dn.FirstOrDefault().DnId;
                mr.DocRefNo = dn.FirstOrDefault().DocumentNumber;
                mr.EmpId = prItem.FirstOrDefault().PurchaseRequest.PurchaseEmpId;
                mr.EventId = 11;
                mr.JobSiteId = prItem.FirstOrDefault().PurchaseRequest.PurchaseJobSiteId;
                mr.MoneyReqDate = DateTime.Now;
                mr.MoneyReqName = " ขอเบิกเงินซื้อจ้างพัสดุ " + dn.FirstOrDefault().DocumentNumber;//model.PurchaseOrder.Name;
                mr.MoneyReqNamePlus = null;
                mr.RecordStatus = KPIS.GERP.Models.SYS.RecordStatus.WaitStatus;
                mr.RefNo = RefNo;
                mr.Remarks = null;
                mr.TranferJobSiteId = null;
                mr.TranferJobSiteName = null;
                mr.TranGrpId = null;
                mr.TravelReqId = null;
                mr.TravelTypeId = null;
                mr.UpdatedBy = null;
                mr.UpdatedWhen = null;
                mr.UseAdvance = "0";
                mr.WorkAreaId = null;///////hrm_mp_job_sites

                //model money_request_item
                mr.Amount = (double?)poIns.Sum(p => p.NetAmount);
                mr.AmountVat = (double?)poIns.Sum(p => p.VatAmount);
                mr.MoneyReqDtlName = poIns.FirstOrDefault().Descr;
                mr.BgProjOperExpId = pprItem.FirstOrDefault().ProjOperExpId;
                #endregion[assign model]

                using (var srv = new DeliveryNoteService.DeliveryNoteServiceClient())
                {
                    srv.InsertMoneyRequest(mr);
                    if (string.IsNullOrWhiteSpace(dn.FirstOrDefault().Code))
                    {
                        throw new Exception("Error DnCode is null");
                    }
                    srv.UpdateUsed(dn.FirstOrDefault(), Class.GimsMembership.CurrentEmpId);
                }

                return RedirectToAction("Index");
            }
            catch (Exception ex)
            {
                ModelState.AddModelError(ErrorLocalization.Error, ErrorLocalization.ErrorWhileDeleteData);
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                return Json(new { success = false });
            }
        }

        private bool ValidateDeliveryData(DeliveryNote model, List<PurchaseOrderInstallmentItem> poIns
            , List<PurchaseRequestItem> prItem,List<PrePurchaseRequestItem> pprItem)
        {
            bool ret = false;
            #region[old]
            ////List<PurchaseOrder> po = new List<PurchaseOrder>();
            //List<PurchaseOrderInstallmentItem> poIns = new List<PurchaseOrderInstallmentItem>();
            //using (var posrv = new PurchaseOrderService.PurchaseOrderServiceClient())
            //{
            //    //po = posrv.Select(new Models.ICM.PurchaseOrder { PoId = (int)model.PoId }, new[] { "Bidder", "MasPurchaseType", "DurationType" }).ToList();
            //    poIns = posrv.SelectPoInstallmentItem(new Models.ICM.PurchaseOrderInstallmentItem { PoInstallId = (int)model.PoInstallId }, new[] { "BidderQuotation", "BidderQuotation.Bidder" }).ToList();                
            //}
            ////List<PurchaseOrderItem> poItem = new List<PurchaseOrderItem>();
            ////using (var posrv = new PurchaseOrderItemService.PurchaseOrderItemServiceClient())
            ////{
            ////    poItem = posrv.Select(new Models.ICM.PurchaseOrderItem { PoId = (int)model.PoId }, new[] { "BidderQuotation", "BidderQuotation.PurchaseRequestItem", "BidderQuotation.PurchaseRequestItem" }).ToList();
            ////}

            //List<PurchaseRequestItem> prItem = new List<PurchaseRequestItem>();
            //using (var prsrv = new PurchaseRequestItemService.PurchaseRequestItemServiceClient())
            //{
            //    prItem = prsrv.Select(new Models.ICM.PurchaseRequestItem { PrItemId = (int)poIns.FirstOrDefault().BidderQuotation.PrItemId }, new[] { "PurchaseRequest", "MasBudgetExpense" }).ToList();
            //}
            ////get ppritem data
            //List<PrePurchaseRequestItem> pprItem = new List<PrePurchaseRequestItem>();
            //using (var ppritem = new PrePurchaseRequestItemService.PrePurchaseRequestItemServiceClient())
            //{
            //    pprItem = ppritem.Select(new PrePurchaseRequestItem { PrItemId = poIns.FirstOrDefault().BidderQuotation.PrItemId }, new[] { "ProjectOperationExpense" }).ToList();
            //}
            #endregion[old]

            string RefNo = string.Empty;
            using (var sys = new SysMasterService.SysMasterServiceClient())
            {
                RefNo = sys.GenNewRefNo2("fim_money_requests", "RQ", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
            }

            /////////model money_request
            MoneyRequest mr = new MoneyRequest();
            mr.AdvanceBgSourceId = null;
            mr.Allow = "1";
            mr.ApproveId = null;
            mr.BgExpGrpId = null; ///////bgm_mas_expense_group
            mr.BgProjOperId = pprItem.FirstOrDefault().ProjectOperationExpense.ProjOperId;            
            mr.BgSourceId = pprItem.FirstOrDefault().BgSourceId;
            mr.BgYear = prItem.FirstOrDefault().PurchaseRequest.BgYear;
            mr.BorrowEndDate = null;
            mr.BorrowNo = null;
            mr.BorrowReturnDate = null;
            mr.BorrowStartDate = null;
            mr.BorrowTypeId = null;
            mr.ContactId = poIns.FirstOrDefault().BidderQuotation.Bidder.ConId;//model.PurchaseOrder.Bidder.ConId;
            mr.CreatedBy = Class.GimsMembership.CurrentEmpId;
            mr.CreatedWhen = DateTime.Now;
            mr.CreditCardNo = null;
            mr.DebtorContactId = null;
            mr.DebtorEmpId = null;
            mr.DnId = model.DnId;
            mr.DocRefNo = model.DocumentNumber;
            mr.EmpId = prItem.FirstOrDefault().PurchaseRequest.PurchaseEmpId;
            mr.EventId = 11;
            mr.JobSiteId = prItem.FirstOrDefault().PurchaseRequest.PurchaseJobSiteId;
            mr.MoneyReqDate = DateTime.Now;
            mr.MoneyReqName = " ขอเบิกเงินซื้อจ้างพัสดุ " + model.DocumentNumber;//model.PurchaseOrder.Name;
            mr.MoneyReqNamePlus = null;
            mr.RecordStatus = KPIS.GERP.Models.SYS.RecordStatus.WaitStatus;
            mr.RefNo = RefNo;
            mr.Remarks = null;
            mr.TranferJobSiteId = null;
            mr.TranferJobSiteName = null;
            mr.TranGrpId = null;
            mr.TravelReqId = null;
            mr.TravelTypeId = null;
            mr.UpdatedBy = null;
            mr.UpdatedWhen = null;
            mr.UseAdvance = "0";
            mr.WorkAreaId = null;///////hrm_mp_job_sites

           
            //model money_request_item
            mr.Amount = (double?)poIns.Sum(p => p.NetAmount);
            mr.AmountVat = (double?)poIns.Sum(p => p.VatAmount);
            mr.MoneyReqDtlName = poIns.FirstOrDefault().Descr;
            mr.BgProjOperExpId = pprItem.FirstOrDefault().ProjOperExpId;

            using (var srv = new DeliveryNoteService.DeliveryNoteServiceClient())
            {
                srv.InsertMoneyRequest(mr);
            }
           // srv.

            return ret;
        }

        public void ReportDeliveryNote(int DnId)
        {
            var service = new DeliveryNoteService.DeliveryNoteServiceClient();
            var userService = new UserService.UserServiceClient();
            var serviceBidderQuotation = new BidService.BidServiceClient();
            Dictionary<string, string> fields = new Dictionary<string, string>();
            MailMerge mailMerge = new MailMerge();
            var serviceThaiBaht = new PurchaseOrderService.PurchaseOrderServiceClient();

            var modelDelivery =
                service.SelectDeliveryNote(new DeliveryNote {DnId = DnId},
                    new[]
                    {
                        "PurchaseOrder", "PurchaseOrderInstallment", "PurchaseOrder.MasPurchaseType",
                        "PurchaseOrder.Bidder.MasContact", "PurchaseOrder.Bidder.PurchaseRequest.MasPurchaseMethod"
                    })
                    .FirstOrDefault();
            var modelBidderQuotation =
                serviceBidderQuotation.SelectBidderQuotation(
                    new BidderQuotation { BidderId = modelDelivery.PurchaseOrder.BidderId }, null).ToList();

            fields.Add("DnCode", modelDelivery.Code);
            string SubStr = modelDelivery.PurchaseOrder.OrderDate.Value.ToLongDateString();
            string[] SubOrderDate = SubStr.Split(' ');
            string Date = SubOrderDate[0];
            string Month = SubOrderDate[1];
            string Year = SubOrderDate[2];

            fields.Add("Day", Date);
            fields.Add("Month", Month);
            fields.Add("Year", Year);
            fields.Add("POCode", modelDelivery.PurchaseOrder.Code);
            fields.Add("POName", modelDelivery.PurchaseOrder.Name);
            string SubStrDate = modelDelivery.PurchaseOrder.CreatedWhen.Value.ToLongDateString();
            string[] SubCreatedWhen = SubStrDate.Split(' ');
            string CreatedWhen = string.Format("{0} {1} {2}", SubCreatedWhen[0], SubCreatedWhen[1], SubCreatedWhen[2]);
            fields.Add("PODate", CreatedWhen);
            fields.Add("Reason", modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.MasPurchaseMethod.Name);
            fields.Add("Date", DateTime.Now.ToLongDateString());
            decimal SumPrice = Convert.ToDecimal(modelBidderQuotation.ToList().Sum(m => m.NetAmount));
            fields.Add("Price", SumPrice.ToString("#,##0.00"));
            fields.Add("RequireDate", modelDelivery.PurchaseOrder.RequireDate.Value.ToLongDateString());

            fields.Add("SumPrice", SumPrice.ToString("#,##0.00"));
            string ThaiBaht = serviceThaiBaht.ThaiBaht((SumPrice).ToString());
            fields.Add("WordPrice", ThaiBaht); 
            fields.Add("RefName", modelDelivery.PurchaseOrder.Bidder.MasContact.NameTh);
            //Adding on 23/05/57
            fields.Add("PrId", modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.Code);
            //Adding on 9/06/57
            fields.Add("InvNum", modelDelivery.InvoiceNumber ?? "................");
            //Adding on 18/07/57
            fields.Add("Name", modelDelivery.Name);
            //Adding on 10/11/57
            fields.Add("InvDate", modelDelivery.InvoiceDate.Value.ToLongDateString());
            fields.Add("RecvDate", modelDelivery.ReceiveDate.Value.ToLongDateString());

            //ดึงรายการตรวจรับพัสดุ ดูตัวอย่างจาก DeliveryNoteItemController
            var serviceDelivItems = new DeliveryNoteItemService.DeliveryNoteItemServiceClient();
            var modelDelivItems = serviceDelivItems.SelectDeliveryNoteItem(
                new Models.ICM.DeliveryNoteItem {DnId = DnId},
                new[]
                {
                    "PoInstallItem", "PoInstallItem.BidderQuotation.PurchaseRequestItem.MasResourceItem",
                    "PoInstallItem.BidderQuotation.PurchaseRequestItem.MasResourceItem.MasUnit"
                });

            double exVatTotal = 0, inVatTotal = 0, vat = 0;

            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("name");
            dt.Columns.Add("qty");
            dt.Columns.Add("unitPrice");
            dt.Columns.Add("amount");

            foreach (var m in modelDelivItems)
            {
                System.Data.DataRow r = dt.NewRow();
                double uPrice = m.ItemPriceExcludeVat ?? 0;
                    ///setValueNumber((((m.PoInstallItem.NetAmount ?? 0) / (m.PoInstallItem.Qty ?? 0)) * 100)/107);
                double amountPrice = uPrice * (m.ItemQty ?? 1);//m.ExcludeVatAmount ?? 0; //uPrice * (m.PoInstallItem.Qty ?? 0);

                //inVatTotal += (m.PoInstallItem.NetAmount ?? 0);
                //exVatTotal += amountPrice;

                r["name"] = m.PoInstallItem.BidderQuotation.PurchaseRequestItem.MasResourceItem != null
                    ? m.PoInstallItem.BidderQuotation.PurchaseRequestItem.MasResourceItem.Name
                    : m.PoInstallItem.BidderQuotation.PurchaseRequestItem.Descr;
                r["qty"] = string.Format("{0} {1}", (m.ItemQty ?? 0).ToString("#,##0"),
                    m.PoInstallItem.BidderQuotation.PurchaseRequestItem.MasResourceItem != null
                        ? m.PoInstallItem.BidderQuotation.PurchaseRequestItem.MasResourceItem.MasUnit.Name
                        : "");
                r["unitPrice"] = uPrice.ToString("#,##0.00");
                r["amount"] = amountPrice.ToString("#,##0.00");

                dt.Rows.Add(r);
            }

            vat = modelDelivItems.Sum(m => m.VatAmount) ?? 0;
            inVatTotal = modelDelivItems.Sum(m => m.ItemPriceIncludeVat * m.ItemQty) ?? 0;
            exVatTotal = modelDelivItems.Sum(m => m.ItemPriceExcludeVat * m.ItemQty) ?? 0;

            fields.Add("inVatTotal", inVatTotal.ToString("#,##0.00"));
            fields.Add("exVatTotal", exVatTotal.ToString("#,##0.00"));
            fields.Add("vatTotal", vat.ToString("#,##0.00"));
            string ThaiBaht2 = serviceThaiBaht.ThaiBaht((inVatTotal).ToString()); 
            fields.Add("inVatTotalThai", ThaiBaht2);

            Dictionary<string, System.Data.DataTable> dtDic = new Dictionary<string, System.Data.DataTable>();

            dtDic.Add("Items", dt);

            //var serviceCommittee = new CommitteeService.CommitteeServiceClient();
            //var Dice = (dynamic)null;

            ////เพิ่ม ! เข้าไปเนื่องจากชื่อกรรมการตรวจรับไม่ขึ้น เพราะเช็คเงื่อนไขด้วย .Any() พอมีค่ากลับมาจึงได้ True
            ////ตัวแปร Dice ที่จะส่งค่ากรรมการตรวจรับเข้าไปพิมพ์ จึงถูกกำหนดค่าเป็น null
            //if (!serviceCommittee.GetUserCommitee(modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.CommitteeGroupId ?? 0).Any())
            //{
            //    Dice = null;
            //}
            //else
            //{
            //    Dice = serviceCommittee.GetUserCommitee(modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.CommitteeGroupId ?? 0).ToList();
            //}

            //ส่วนดึงข้อมูลรายชื่อกรรมการตรวจรับ
            var serviceCommitteeGroup = new CommitteeGroupService.CommitteeGroupServiceClient();
            var serviceEmp = new EmployeeService.EmployeeServiceClient();

            string[] _committeeIncludeSubclass = new[]
            {
                "Committees.CommitteeType", "Committees.CommitteePosition", "Committees.CommitteeFunction",
                "Committees.Employee.Position"
            };

            if (!modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.CommitteeGroupId.HasValue)
            {
                var cg = new CommitteeGroup();
                cg.RecordStatus = "U";
                cg.CreateByPprId = null;
                var db = new GERPContext();
                var pri = db.PurchaseRequestItems.FirstOrDefault(o => o.PrId == modelDelivery.PurchaseOrder
                        .Bidder.PurchaseRequest.PrId);
                if (pri != null)
                {
                    var ppri = db.PrePurchaseRequestItems.FirstOrDefault(o => o.PrItemId == pri.PrItemId);
                    if (ppri != null)
                    {
                        cg.CreateByPprId = ppri.PprId;
                    }
                }
                db.CommitteeGroups.Add(cg);
                db.SaveChanges();
                var prXX = db.PurchaseRequests.Find(modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.PrId);
                prXX.CommitteeGroupId = cg.CommitteeGroupId;
                db.Entry(prXX).State = EntityState.Modified;
                db.SaveChanges();
                modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.CommitteeGroupId = prXX.CommitteeGroupId;
            }
            var ModelCommittee = (serviceCommitteeGroup.Select(new CommitteeGroup
            {
                CommitteeGroupId = modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.CommitteeGroupId.Value
            }, _committeeIncludeSubclass).First().Committees ?? new List<Committee>()).Where(o => o.RecordStatus != "D").ToArray();

            List<Dictionary<string, string>> ListCommittee = new List<Dictionary<string, string>>();

            if (ModelCommittee != null)
            {
                var group = ModelCommittee.GroupBy(x => x.ComFunctionId).ToList();

                for (int i = 0; i < group.Count(); i++)
                {
                    for (int j = 0; j < group[i].Count(); j++)
                    {
                        if (group[i].ElementAt(j).EmpId.HasValue)
                        {
                            Dictionary<string, string> dics = new Dictionary<string, string>();
                            var tempModel = serviceEmp.Select(new Employee {EmpId = group[i].ElementAt(j).EmpId.Value}
                                , new[] {"MasNamePrefix"}, true).FirstOrDefault();

                            dics.Add("FullName", tempModel.FullName);
                            dics.Add("ComPos", group[i].ElementAt(j).CommitteePosition.LookUpName);

                            ListCommittee.Add(dics);
                        }
                    }
                }
            }

            var modelUser =
                userService.GetUsers().Where(o => o.EmpId == Class.GimsMembership.CurrentEmpId).FirstOrDefault();
            fields.Add("PersonName", modelUser.FullName);
            var serviceUser = new EmployeeService.EmployeeServiceClient();
            var modelEmp = serviceUser.Select(new Employee {EmpId = Class.GimsMembership.CurrentEmpId}
                , new[] {"Position.WorkingUpperPosition"}, true).FirstOrDefault();
            //Modified on 23/5/57 เนื่องจากข้อมูลในฐานข้อมูลไม่ตรง
            //var upperPositionId = modelEmp.Position.UpperPosId;
            //var upperPositionId = 78;
            var upperPositionId = modelEmp.Position.WorkingUpperPosId;
            var modelEmpHead = serviceUser.Select(new Employee {PosId = upperPositionId, RecordStatus = "!D"}
                , new[] {"MasNamePrefix"}, true).FirstOrDefault();
            fields.Add("HeadName", modelEmpHead.FullName);
            var serviceSysUser = new UserService.UserServiceClient();
            // var modelSysUser = serviceSysUser.GetNameApproveTask(modelDelivery.PurchaseOrder.Bidder.PurchaseRequest.Code);
            var modelSysUser = serviceSysUser.GetNameApproveTask(modelDelivery.Code);
            if (modelSysUser == null)
            {
                fields.Add("ApproveName", "");
                fields.Add("PrositionApproveName", "");
            }
            else
            {
                var modelUserApprove = userService.GetUsers().First(o => o.EmpId == modelSysUser.EmpId);
                var Approve = modelUserApprove.FullName ?? "";
                fields.Add("ApproveName", Approve);

                var modelPosition = userService.GetUsers().First(o => o.EmpId == modelSysUser.EmpId);
                var positionName = modelPosition.Employee != null && modelPosition.Employee.Position != null &&
                                   modelPosition.Employee.Position.Name != null
                    ? modelPosition.Employee.Position.Name
                    : "";
                fields.Add("PrositionApproveName", positionName);
            }
            string committeeOrEmployeeText =ModelCommittee.Count() >= 3? "คณะกรรมการ": "เจ้าหน้าที่";
            fields.Add("CommitteeOrEmployee1", committeeOrEmployeeText);
            fields.Add("CommitteeOrEmployee2", committeeOrEmployeeText);
            fields.Add("CommitteeOrEmployee3", committeeOrEmployeeText);
            fields.Add("CommitteeOrEmployee4", committeeOrEmployeeText);
            fields.Add("CommitteeOrEmployee5", committeeOrEmployeeText);
            fields.Add("CommitteeOrEmployee6", committeeOrEmployeeText);
            fields.Add("CommitteeOrEmployee7", committeeOrEmployeeText);
            string purchaseSellerText = "ผู้ขาย";
            switch (modelDelivery.PurchaseOrder.PcTypeId)
            {
                case 1:
                    purchaseSellerText = "ผู้ขาย";
                    break; // ซื้อ
                case 2:
                    purchaseSellerText = "ผู้รับจ้าง";
                    break; // จ้าง
                case 3:
                    purchaseSellerText = "ผู้ให้เช่า";
                    break; // เช่า
                case 4:
                    purchaseSellerText = "ผู้ให้แลกเปลี่ยน";
                    break; // แลกเปลี่ยน
                case 5:
                    purchaseSellerText = "ผู้ซ่อมบำรุง";
                    break; // ซ่อมบำรุง
                case 6:
                    purchaseSellerText = "ผู้รับจ้าง";
                    break; // จ้างออกแบบและควบคุมงาน
                case 7:
                    purchaseSellerText = "ผู้รับจ้าง";
                    break; // จ้างที่ปรึกษา
                case 8:
                    purchaseSellerText = "ผู้รับจ้าง";
                    break; // จ้างเหมาบริการ
                case 9:
                    purchaseSellerText = "ผู้รับจ้าง";
                    break; // จ้างผู้เชี่ยวชาญ       
                default:
                    purchaseSellerText = "ผู้ขาย";
                    break;
            }
            fields.Add("SellerOrContractSeller", purchaseSellerText);

            string bgSourceName = "";
            string activityCode = "";
            using (var db = new GERPContext())
            {
                bgSourceName = (from bgs in db.MasBudgetSources
                    join ppri in db.PrePurchaseRequestItems on bgs.BgSourceId equals ppri.BgSourceId
                    join pri in db.PurchaseRequestItems on ppri.PrItemId equals pri.PrItemId
                    join bq in db.BidderQuotations on pri.PrItemId equals bq.PrItemId
                    join poi in db.PurchaseOrderItems on bq.BidderQuoId equals poi.BidderQuoId
                    where poi.PoId == modelDelivery.PoId
                    select bgs.Name).FirstOrDefault();

                var projectCode = (from p in db.Projects
                                    join pop in db.ProjectOperations on p.ProjId equals pop.ProjId
                               join poe in db.ProjectOperationExpenses on pop.ProjOperId equals poe.ProjOperId
                                    join ppri in db.PrePurchaseRequestItems on poe.ProjOperExpId equals ppri.ProjOperExpId
                                    join pri in db.PurchaseRequestItems on ppri.PrItemId equals pri.PrItemId
                                    join bq in db.BidderQuotations on pri.PrItemId equals bq.PrItemId
                                    join poi in db.PurchaseOrderItems on bq.BidderQuoId equals poi.BidderQuoId
                                    where poi.PoId == modelDelivery.PoId
                                    select p.Code).FirstOrDefault();

                // TotalAmount = db.ProjectExpenses.Where(o => o.ProjId == project.ProjId).Sum(o => o.TotalAmount) ?? 0;
                activityCode = projectCode;
            }


            fields.Add("BgSourceName", bgSourceName);
            fields.Add("ActivityCode", activityCode);

            string strTemplatePath = Server.MapPath("~/Templates/รายงานตรวจรับพัสดุ2.docx");
            string strSaveAsPath = Server.MapPath("~/Temp/");
            string strSaveAsFileName = string.Format("DeliveryNoteReport{0}.docx",
                DateTime.Now.ToString("yyyyMMddHHmmssffff"));

            CreateDeliverItemTable(strTemplatePath, dt.Rows.Count, strSaveAsPath + strSaveAsFileName);
            CommitteeTable(strSaveAsPath + strSaveAsFileName, ListCommittee.Count, strSaveAsPath + strSaveAsFileName);
            if (mailMerge.MergeTemplate(strSaveAsPath + strSaveAsFileName, strSaveAsPath + strSaveAsFileName, fields,
                dtDic, ListCommittee, null))
            {
                Response.Clear();
                Response.ContentType = "text/plain";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + strSaveAsFileName);
                Response.WriteFile(strSaveAsPath + strSaveAsFileName);
                Response.Flush();
                if (true)
                {
                    System.IO.File.Delete(strSaveAsPath + strSaveAsFileName);
                }
                Response.End();
            }
        }

        public void CommitteeTable(string strTemplateFilePath, int rowItem, string saveTemp)
        {
            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                for (int i = 0; i < rowItem; i++)
                {
                    Row rxItem = document.Tables[2].InsertRow(document.Tables[2].RowCount-1);

                    rxItem.Cells[0].Paragraphs[0].Append("\n(ลงชื่อ).......................................................«ComPos" + (i + 1).ToString() + "»\n( «FullName" + (i + 1).ToString() + "» )");
                    rxItem.Cells[0].Paragraphs[0].Alignment = Alignment.center;
                    rxItem.Cells[0].Paragraphs[0].FontSize(16);
                    rxItem.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK")); 

                }

                document.SaveAs(saveTemp);
            }
        }

        public double setValueNumber(double value)
        {
            string[] strPre = value.ToString().Split('.');

            if (strPre.Length > 1 && strPre[1].Length > 2)
            {
                strPre[1] = strPre[1].Substring(0, 2);
                value = Convert.ToDouble(strPre[0] + "." + strPre[1]);
            }

            return value;
        }

        public void CreateDeliverItemTable(string strTemplateFilePath, int row, string saveTemp)
        {
            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                if (document.Tables[1].ColumnCount > 3)
                {
                    //วนลูปรายการในตาราง
                    for (int i = 0; i < row; i++)
                    {
                        Row rx = document.Tables[1].InsertRow(document.Tables[2].RowCount);

                        rx.Cells[0].Paragraphs[0].Append("«" + "name" + (i + 1).ToString() + "»");
                        rx.Cells[0].Paragraphs[0].FontSize(16);
                        rx.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                        rx.Cells[1].Paragraphs[0].Append("«" + "qty" + (i + 1).ToString() + "»");
                        rx.Cells[1].Paragraphs[0].FontSize(16);
                        rx.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[1].Paragraphs[0].Alignment = Alignment.center;

                        rx.Cells[2].Paragraphs[0].Append("«" + "unitPrice" + (i + 1).ToString() + "»");
                        rx.Cells[2].Paragraphs[0].Alignment = Alignment.right;
                        rx.Cells[2].Paragraphs[0].FontSize(16);
                        rx.Cells[2].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                        rx.Cells[3].Paragraphs[0].Append("«" + "amount" + (i + 1).ToString() + "»");
                        rx.Cells[3].Paragraphs[0].Alignment = Alignment.right;
                        rx.Cells[3].Paragraphs[0].FontSize(16);
                        rx.Cells[3].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[0].Width = 300;
                        rx.Cells[1].Width = 100;
                        rx.Cells[2].Width = 100;
                        rx.Cells[3].Width = 100;
                    }                  

                    document.SaveAs(saveTemp);
                }
            }
        }
    }
}
